package rdbms;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import DAO.DatabasePool;
import DAO.ENSequenceDAO;


import model.DIClass;
import model.ENSequence;

public class RDBMSDIClassDAO {

	private String idConnection;
	private ENSequenceDAO sequence;

	private static String INSERT = "insert into DI_CLASS "
			+ "(class_id, description) values (?, ?) ";

	private static String UPDATE = "update DI_CLASS "
			+ "set description = ? where class_id = ? ";

	private static String DELETE = "delete from DI_CLASS where class_id = ? ";

	private static String DELETE_ALL = "delete from DI_CLASS where class_id <> 0 ";

	private static String SELECT_BY_ID = "select description from DI_CLASS "
			+ "where class_id = ? ";

	private static String SELECT_BY_DESCRIPTION = "select class_id from DI_CLASS "
			+ "where description  = ? ";

	private static String SELECT_ALL = "select class_id, description from DI_CLASS";

	public RDBMSDIClassDAO(final String idConnection,
			final ENSequenceDAO sequence) throws SQLException {
		this.idConnection = idConnection;
		this.sequence = sequence;
	}

	public boolean insert(DIClass diClass) throws Exception {
		boolean success = false;
		Connection conn = null;
		PreparedStatement pst = null;
		try {
			conn = DatabasePool.getConnection(idConnection);
			diClass.setClassId(sequence.nextval(ENSequence.CLASS_ID));
			pst = conn.prepareStatement(INSERT);
			pst.setInt(1, diClass.getClassId());
			pst.setString(2, diClass.getDescription());
			pst.execute();
			success = true;
		} catch (Exception e) {
			throw new Exception("Exception while inserting Class.", e);
		} finally {
			if (success) {
				conn.commit();
				System.out.println("Classe Inserida - Commit");
			} else {
				conn.rollback();
				System.out.println("Classe Nao Inserida - Rollback");
			}
			if (pst != null)
				pst.close();
		}
		return success;
	}

	public boolean delete(int id) throws Exception {
		boolean success = false;
		Connection conn = null;
		PreparedStatement pst = null;
		try {
			conn = DatabasePool.getConnection(idConnection);
			pst = conn.prepareStatement(DELETE);
			pst.setInt(1, id);
			pst.execute();
			success = true;
		} catch (Exception e) {
			throw new Exception("Exception while deleting Class.", e);
		} finally {
			if (success) {
				conn.commit();
				System.out.println("Classe Deletada - Commit");
			} else {
				conn.rollback();
				System.out.println("Classe Nao Deletada - Rollback");
			}
			if (pst != null)
				pst.close();
		}
		return success;
	}

	public boolean deleteAll() throws Exception {
		boolean success = false;
		Connection conn = null;
		PreparedStatement pst = null;
		try {
			conn = DatabasePool.getConnection(idConnection);
			pst = conn.prepareStatement(DELETE_ALL);
			pst.execute();
			success = true;
		} catch (Exception e) {
			throw new Exception("Exception while deleting(all) Classe.", e);
		} finally {
			if (success) {
				conn.commit();
				System.out.println("Classes Deletadas - Commit");
			} else {
				conn.rollback();
				System.out.println("Dominios Nao Deletados - Rollback");
			}
			if (pst != null)
				pst.close();
		}
		return success;
	}

	public DIClass find(int id) throws Exception {
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rss = null;
		DIClass DIClass = null;
		try {
			conn = DatabasePool.getConnection(idConnection);
			pst = conn.prepareStatement(SELECT_BY_ID);
			pst.setInt(1, id);
			rss = pst.executeQuery();
			if (rss.next()) {
				DIClass = new DIClass(id, rss.getString(1));
			}
			return DIClass;
		} catch (Exception e) {
			throw new Exception("Exception while retrieving DIClass.", e);
		} finally {
			if (rss != null)
				rss.close();
			if (pst != null)
				pst.close();
		}
	}

	public DIClass find(String description) throws Exception {
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rss = null;
		DIClass DIClass = null;
		try {
			conn = DatabasePool.getConnection(idConnection);
			pst = conn.prepareStatement(SELECT_BY_DESCRIPTION);
			pst.setString(1, description);
			rss = pst.executeQuery();
			if (rss.next()) {
				DIClass = new DIClass(rss.getInt(1), description);
			}
			return DIClass;
		} catch (Exception e) {
			throw new Exception("Exception while retrieving DIClass.", e);
		} finally {
			if (rss != null)
				rss.close();
			if (pst != null)
				pst.close();
		}
	}

	public boolean update(DIClass diClass) throws Exception {
		boolean success = false;
		Connection conn = null;
		PreparedStatement pst = null;
		try {
			conn = DatabasePool.getConnection(idConnection);
			pst = conn.prepareStatement(UPDATE);
			pst.setString(1, diClass.getDescription());
			pst.setInt(2, diClass.getClassId());
			pst.execute();
			success = true;
		} catch (Exception e) {
			throw new Exception("Exception while updating DIClass.", e);
		} finally {
			if (pst != null)
				pst.close();
		}
		return success;
	}

	public List<DIClass> list(int noi) throws Exception {
		List<DIClass> list = null;
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rss = null;
		int ctr = 0;
		try {
			conn = DatabasePool.getConnection(idConnection);
			list = new ArrayList<DIClass>();
			pst = conn.prepareStatement(SELECT_ALL);
			rss = pst.executeQuery();
			while (rss.next()) {
				if (++ctr > noi)
					break;
				DIClass DIClass = new DIClass(rss.getInt(1), rss.getString(2));
				list.add(DIClass);
			}
			return list;
		} catch (Exception e) {
			throw new Exception("Exception while retrieving DIClass.", e);
		} finally {
			if (rss != null)
				rss.close();
			if (pst != null)
				pst.close();
		}
	}
}
